package com.wym.lottery.dao;

import java.util.List;

import com.wym.lottery.model.Bill;
import com.wym.lottery.model.Ticket;
import com.wym.lottery.util.CheckData;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "lottery.db";
	private static final int DATABASE_VERSION = 1;
	public final static String T_Lottery = "t_lottery";
	public final static String T_Bill = "t_bill";

	private final String CREATE_TABLE_SQL = "CREATE TABLE t_lottery (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + "t_num  TEXT(5) NOT NULL," + "t_money  INTEGER NOT NULL," + "t_type  TEXT NOT NULL," + "t_time  TEXT NOT NULL," + "t_odds  TEXT NOT NULL," + "t_state INTEGER default 0," + "t_scope  TEXT NOT NULL)";

	// type TEXT(5) NOT NULL,作为预留字段
	private final String CREATE_TABLE_SQL1 = "CREATE TABLE t_bill(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, b_scope TEXT(5) NOT NULL, b_time TEXT NOT NULL, b_type_sm TEXT(5) NOT NULL, b_sell_money INTEGER NOT NULL, b_pay_money INTEGER NOT NULL, b_win_number TEXT(4) NOT NULL)";

	
	public MyDatabaseHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 第一次使用数据库时自动建表
		db.execSQL(CREATE_TABLE_SQL);
		db.execSQL(CREATE_TABLE_SQL1);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public boolean insert(Ticket t) {
		SQLiteDatabase db = getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("t_num", t.getNum());
		values.put("t_money", t.getMoney());
		values.put("t_type", t.getType());
		values.put("t_time", t.getTime());
		values.put("t_scope", t.getScope());
		values.put("t_odds", t.getOdds());
		return db.insert(T_Lottery, null, values) != -1 ? true : false;
	}

	// 将数据库中的号码和开奖号码进行比对，如果是中奖的，将号码的state置为1
	public void verify(Bill b) {
		for (int i = 1; i <= 14; i++) {
			verifyNumber(b.getScope(), i + "", b.getWin_number());
		}
	}

	private void verifyNumber(String scope, String type, String num) {
		SQLiteDatabase db = getReadableDatabase();
		if ("2".equals(type) || "3".equals(type)) {
			// 取得中奖号码转化成同上的数列
			List<String> list = CheckData.resolve2Tor3T(type, num);
			String sql = "update " + T_Lottery + " set t_state=1 where t_scope=? and t_type=?  " + jointNum(list);
			db.execSQL(sql, new String[] { scope, type });
		} else {
			// 将开奖号码按照类别分解
			String num1 = CheckData.resolveWinNumber(type, num);
			ContentValues values = new ContentValues();
			values.put("t_state", 1);
			db.update(T_Lottery, values, "t_scope=? and t_type=? and t_num=?", new String[] { scope, type, num1 });
		}
	}

	// 拼接sql的数列语句
	private String jointNum(List<String> list) {
		String str = "t_num=";
		String or = " or ";
		StringBuffer sb = new StringBuffer("and (");
		for (int i = 0; i < list.size(); i++) {
			sb.append(str).append(list.get(i));
			if (i != list.size() - 1)
				sb.append(or);
		}

		sb.append(")");
		return sb.toString();
	}

	// 每期都会生成六种账单，分别是"四现", "三现", "二现", "四码", "三定", "二定"
	public void insert(Bill b) {
		SQLiteDatabase db = getReadableDatabase();
		String[] type_sm = new String[] { "四现", "三现", "二现", "四码", "三定", "二定" };
		String[] type_limits = new String[] { "(t_type=1)", "(t_type=2)", "(t_type=3)", "(t_type=4)", "(4<t_type and t_type<=8)", "(9<t_type and t_type <=14)" };

		for (int i = 0; i < type_limits.length; i++) {
			String find_sell_money = "(select COALESCE(SUM(t_money),0) from t_lottery where t_scope=" + b.getScope() + " and " + type_limits[i] + ")";
			String find_pay_money = "(select  COALESCE(SUM(t_money*t_odds),0) from t_lottery where t_scope=" + b.getScope() + " and t_state=1 and" + type_limits[i] + ")";

			db.execSQL("insert into t_bill ( b_scope, b_time, b_type_sm, b_win_number, b_sell_money, b_pay_money) values('" + b.getScope() + "','" + b.getDate() + "','" + type_sm[i] + "','" + b.getWin_number() + "'," + find_sell_money + "," + find_pay_money + ")");
		}

	}

	public Cursor QueryData(String table_name, String scope) {
		String sql = "select * from " + table_name + " where b_scope=?";
		return this.getReadableDatabase().rawQuery(sql, new String[] { scope });
	}

}
